<?php defined('SYSPATH') OR die('No direct access allowed.');

/**
 *库存查询类方法
 * @package inventory
 * @auchor  taj
 */
class  Inventory
{

    /**
     *查询产品sku是否存在id
     * @param  string $sku 产品sku
     * @return int 反馈对应sku的产品id，没有对应的sku就反馈0
     */

    public static function select_productid($sku)
    {
        //START     ----------------------------------CWY更新代码-------------------------------------
        $product_id = Skusystemapi::getSku($sku);
        if($product_id){
            return $product_id['id'];
        }else{
            return 0;
        }
//        $product_sql = "SELECT products_id FROM erp_products_data WHERE products_sku='".$sku."'";
//        $product_id  = DB::query(Database::SELECT,$product_sql)
//            ->execute('readonlyerp')
//            ->as_array();
//        if(!empty($product_id)){
//            return $product_id[0]['products_id'];
//        }else{
//            return 0;
//        }
        //END     ----------------------------------CWY更新代码-------------------------------------
    }

    /**
     *查询sku产品的相关库存信息
     * @param string 产品的sku
     * @return array $stocks 对应sku的相关库存数据array('wo_num'=>,'do_num'=>,'actual_num'=>,'op_print_num'=>,'pp_num'=>,'actual_available_num')
     */
    public static function select_stock($sku)
    {

        $stocks = array();
        $date   = date('Y-m',time());
        $date   = $date.'-01';

        $inventory = ORM::factory('inventory')
            ->where('inventory_date','=',$date)
            ->where('sku','=',$sku)
            ->find();
        if(empty($inventory->id)){

            $stocks['opening_num'] = 0;
        }else{

            $stocks['opening_num'] = $inventory->quantity;
        }
        //入库量
        $sel_wo = "SELECT sum(warehouse_quantity) as wo_num FROM warehouseorders WHERE sku='".$sku."' AND warehouse_date >='".$date."'";
        $wo_num = DB::query(Database::SELECT,$sel_wo)
            ->execute('newerp')
            ->as_array();
        if(empty($wo_num[0]['wo_num'])){
            $stocks['wo_num'] = 0;
        }else{
            $stocks['wo_num'] = $wo_num[0]['wo_num'];
        }

        //出库量
        $sel_do = "SELECT sum(quantity) as do_num FROM deliveryorders WHERE sku='".$sku."'  AND delivery_date>= '".$date."'";
        $do_num = DB::query(Database::SELECT,$sel_do)
            ->execute('newerp')
            ->as_array();
        if(empty($do_num[0]['do_num'])){
            $stocks['do_num'] = 0;
        }else{
            $stocks['do_num'] = $do_num[0]['do_num'];
        }

        //实际库存量（所有已经仓库但还没离开仓库的库存）
        $stocks['actual_num'] = $stocks['opening_num']+$wo_num[0]['wo_num']-$do_num[0]['do_num'];

        //订单实际占用的库存量=总未发量
        //20150113 gqc 添加　and eo.orders_type NOT IN (41,42,49,44)
        $sel_op       = "SELECT SUM(item_count) as op_print_num FROM  erp_orders eo,erp_orders_products eop
        WHERE eo.erp_orders_id = eop.erp_orders_id AND eo.orders_type!=-1 and eo.orders_type NOT IN (41,42,49,44) AND eo.orders_status IN (1,3,4,8,102) AND eop.orders_sku = '".$sku."'";
        $op_print_num = DB::query(Database::SELECT,$sel_op)
            ->execute('readonlyerp')
            ->as_array();
        //查找所有已打印的订单物品号
        $sql_print = "SELECT eo.`erp_orders_id`,eop.orders_products_id FROM erp_orders eo, erp_orders_products eop
                    WHERE eo.erp_orders_id = eop.erp_orders_id AND eo.orders_status =4 AND eop.orders_sku = '".$sku."'";
        $prints = DB::query(Database::SELECT,$sql_print)
            ->execute('readonlyerp')
            ->as_array();
        $print_num = array();
        if(!empty($prints)){

        $prints_string = Arr::pluck($prints,'orders_products_id');
        $prints_string = Arr::unpack_string($prints_string,FALSE,",");
        //查找对应的订单物品号哪些已出库但还没发货的
        $sql_print_num = "SELECT sum(`quantity`) as print_num FROM `deliveryorders` WHERE `orders_products_id` IN (".$prints_string.")";

        $print_num = DB::query(Database::SELECT,$sql_print_num)
            ->execute('newerp')
            ->as_array();
        }
        if(empty($op_print_num[0]['op_print_num'])){
            $stocks['op_print_num'] = 0;
        }else{
            $stocks['op_print_num'] = $op_print_num[0]['op_print_num'];
        }
        //未发量需要减去已扫描出库但还没发货的库存
        if(!empty($print_num))$stocks['op_print_num'] = $stocks['op_print_num']-$print_num[0]['print_num'];

        //总在途量
        $stocks['pp_num'] = Service_Warningcount::get_inventory_onway($sku);

        //实际库存可用量=实际库存量-订单总未发量
        $stocks['actual_available_num'] = $stocks['actual_num']-$stocks['op_print_num'];

        //总可用量
        $stocks['all_available_num'] = $stocks['actual_available_num']+$stocks['pp_num'];
        return $stocks;
    }

 /**
     *查询sku可用库存给转换器
     * @param string 产品的sku
     * @return array $stocks 对应sku的相关库存数据array('wo_num'=>,'do_num'=>,'actual_num'=>,'op_print_num'=>,'pp_num'=>,'actual_available_num')
     */
    public static function select_adapterstock($sku)
    {
		if($sku!=''){

        $stocks = array();
        $date   = date('Y-m',time());
        $date   = $date.'-01';

        $inventory = ORM::factory('inventory')
            ->where('inventory_date','=',$date)
            ->where('sku','=',$sku)
            ->find();
        if(empty($inventory->id)){

            $stocks['opening_num'] = 0;
        }else{

            $stocks['opening_num'] = $inventory->quantity;
        }
        //入库量
        $sel_wo = "SELECT sum(warehouse_quantity) as wo_num FROM warehouseorders WHERE sku='".mysql_escape_string($sku)."' AND warehouse_date >='".$date."'";
        $wo_num = DB::query(Database::SELECT,$sel_wo)
            ->execute('newerp')
            ->as_array();
        if(empty($wo_num[0]['wo_num'])){
            $stocks['wo_num'] = 0;
        }else{
            $stocks['wo_num'] = $wo_num[0]['wo_num'];
        }

        //出库量
        $sel_do = "SELECT sum(quantity) as do_num FROM deliveryorders WHERE sku='".mysql_escape_string($sku)."'  AND delivery_date>= '".$date."'";
        $do_num = DB::query(Database::SELECT,$sel_do)
            ->execute('newerp')
            ->as_array();
        if(empty($do_num[0]['do_num'])){
            $stocks['do_num'] = 0;
        }else{
            $stocks['do_num'] = $do_num[0]['do_num'];
        }

        //实际库存量（所有已经仓库但还没离开仓库的库存）
        $stocks['actual_num'] = $stocks['opening_num']+$wo_num[0]['wo_num']-$do_num[0]['do_num'];
        return $stocks['actual_num'];

//        //订单实际占用的库存量=总未发量
//        $sel_op       = "SELECT SUM(item_count) as op_print_num FROM  erp_orders eo,erp_orders_products eop
//        WHERE eo.erp_orders_id = eop.erp_orders_id AND eo.orders_type!=-1 AND eo.orders_status IN (1,3,4,8) AND eop.orders_sku = '".mysql_real_escape_string($sku)."'";
//        $op_print_num = DB::query(Database::SELECT,$sel_op)
//            ->execute('readonlyerp')
//            ->as_array();
//        //查找所有已打印的订单物品号
//        $sql_print = "SELECT eo.`erp_orders_id`,eop.orders_products_id FROM erp_orders eo, erp_orders_products eop
//                    WHERE eo.erp_orders_id = eop.erp_orders_id AND eo.orders_status =4 AND eop.orders_sku = '".mysql_real_escape_string($sku)."'";
//        $prints = DB::query(Database::SELECT,$sql_print)
//            ->execute('readonlyerp')
//            ->as_array();
//        $print_num = array();
//        if(!empty($prints)){
//
//        $prints_string = Arr::pluck($prints,'orders_products_id');
//        $prints_string = Arr::unpack_string($prints_string,FALSE,",");
//        //查找对应的订单物品号哪些已出库但还没发货的
//        $sql_print_num = "SELECT sum(`quantity`) as print_num FROM `deliveryorders` WHERE `orders_products_id` IN (".$prints_string.")";
//
//        $print_num = DB::query(Database::SELECT,$sql_print_num)
//            ->execute('newerp')
//            ->as_array();
//        }
//        if(empty($op_print_num[0]['op_print_num'])){
//            $stocks['op_print_num'] = 0;
//        }else{
//            $stocks['op_print_num'] = $op_print_num[0]['op_print_num'];
//        }
//        //未发量需要减去已扫描出库但还没发货的库存
//        if(!empty($print_num))$stocks['op_print_num'] = $stocks['op_print_num']-$print_num[0]['print_num'];
//
//        //实际库存可用量=实际库存量-订单总未发量
//        $stocks['actual_available_num'] = $stocks['actual_num']-$stocks['op_print_num'];
//
//        return $stocks['actual_available_num'];
	}else{
		return 0;
	}
    }
    /**
     *查询sku产品的本月库存数量及金额
     * @param string $sku 产品sku
     * @return array $costs 反馈查询的sku的本月库存成本数据
     */

    public static function select_num_costs($sku)
    {
        $costs = array();
        $date  = date('Y-m',time());
        $date  = $date.'-01';
        $warehouse_id = Kohana::$config->load('warehouse')->warehouse_id;

        $re                        = basic::load_sku_info($sku);
        $costs['sku']              = $sku;
        $costs['sku_name']         = $re['products_name_cn'];
        $costs['sku_standard']     = $re['size'];
        $sql="SELECT id,place 
              FROM skusystem_places 
              WHERE id= ANY(
                  SELECT place_id 
                  FROM skusystem_place_skus 
                  WHERE isavailable=1 
                  AND sku='".$sku."') 
              AND warehouse_id={$warehouse_id}";
        $costs['storage_position'] = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('id','place');
        //求期初值
        $inventory = ORM::factory('inventory')
            ->where('inventory_date','=',$date)
            ->where('sku','=',$sku)
            ->find();
        if(empty($inventory->id)){
            $costs['opening_num']  = 0;
            $costs['opening_cost'] = 0;
        }else{
            $costs['opening_num']  = $inventory->quantity;
            $costs['opening_cost'] = round($inventory->cost,2);
        }

        //求本期入库值
        $wo_num_sql = "SELECT sum(warehouse_quantity) as wo_num,sum(cost) as cost,sku,sku_name,sku_standard,storage_position FROM warehouseorders WHERE sku='".$sku."' AND warehouse_date >='".$date."'";
        $wo_num     = DB::query(Database::SELECT,$wo_num_sql)
            ->execute('newerp')
            ->as_array();
        if(empty($wo_num[0]['wo_num'])){
            $costs['wo_num']  = 0;
            $costs['wo_cost'] = 0;
        }else{
            $costs['wo_num']  = $wo_num[0]['wo_num'];
            $costs['wo_cost'] = round($wo_num[0]['cost'],2);
        }

        //求本期出库值
        $do_num_sql = "SELECT sum(quantity) as do_num,sum(cost) as cost FROM deliveryorders WHERE sku='".$sku."' AND delivery_date>= '".$date."'";
        $do_num     = DB::query(Database::SELECT,$do_num_sql)
            ->execute('newerp')
            ->as_array();
        if(empty($do_num[0]['do_num'])){
            $costs['do_num']  = 0;
            $costs['do_cost'] = 0;
        }else{
            $costs['do_num'] = $do_num[0]['do_num'];

            //$supplier = new Service_Common();
            //$price = $supplier->get_sku_price($sku);

            $costs['do_cost'] = round($do_num[0]['cost'],2);
        }

        //求本期结存值
        $costs['ending_num']  = $costs['opening_num']+$costs['wo_num']-$costs['do_num'];
        $costs['ending_cost'] = $costs['opening_cost']+$costs['wo_cost']-$costs['do_cost'];

        return $costs;
    }

    //根据sku和库存查询开始时间和结束时间，查出库存成本
    public static function select_sku_num_cost($sku,$date_from,$date_to)
    {

        $big_sku  = strtoupper($sku);
        $day      = date_format(date_create($date_from),'d');
        $open_day = date_format(date_create($date_from),'Y-m-01');
        $day2     = date_format(date_create($date_to),'d');
        $current  = array();

        //求每月的库存期初值
        $sql_openinventory = "SELECT `sku`,`quantity`,`cost`  FROM `inventories` WHERE `sku`='".$big_sku."' AND `inventory_date`='".$open_day."'";
        $openinventory     = DB::query(Database::SELECT,$sql_openinventory)
            ->execute('newerp')
            ->as_array();
        if(isset($openinventory[0]['sku'])){
            $current['opening_num']  = $openinventory[0]['quantity'];
            $current['opening_cost'] = $openinventory[0]['cost'];
        }else{
            $current['opening_num']  = 0;
            $current['opening_cost'] = 0;
        }

        //加减查询的开始时间的出入库值，作为查询时间点的期初值
        if($day!=01){

            $sql_open_current_wo = "SELECT sum(`warehouse_quantity`)  as num,sum(cost) as cost FROM `warehouseorders` WHERE `sku`='".$big_sku."' AND  `warehouse_date`>='".$open_day."' AND warehouse_date<'$date_from'";
            $open_current_wo     = DB::query(Database::SELECT,$sql_open_current_wo)
                ->execute('newerp')
                ->as_array();
            if(isset($open_current_wo[0]['num'])){
                $current['opening_num']  = $current['opening_num']+$open_current_wo[0]['num'];
                $current['opening_cost'] = $current['opening_cost']+$open_current_wo[0]['cost'];
            }

            $sql_open_current_do = "SELECT sum(`quantity`) as num,sum(cost) as cost FROM `deliveryorders` WHERE `sku`='".$big_sku."' AND `delivery_date`>='".$open_day."' AND  delivery_date<'".$date_from."'";

            $open_current_do = DB::query(Database::SELECT,$sql_open_current_do)
                ->execute('newerp')
                ->as_array();

            if($open_current_do[0]['num']){
                $current['opening_num']  = $current['opening_num']-$open_current_do[0]['num'];
                $current['opening_cost'] = $current['opening_cost']-$open_current_do[0]['cost'];
            }
        }

        //求查询时间段的入库量及成本
        $sql_current_wo = "SELECT sum(`warehouse_quantity`)  as num,sum(cost) as cost FROM `warehouseorders` WHERE `sku`='".$big_sku."' AND  `warehouse_date`>='".$date_from."' AND warehouse_date<='$date_to 23:59:59'";
        $current_wo     = DB::query(Database::SELECT,$sql_current_wo)
            ->execute('newerp')
            ->as_array();
        if(isset($current_wo[0]['num'])){
            $current['wo_num']  = $current_wo[0]['num'];
            $current['wo_cost'] = $current_wo[0]['cost'];
        }else{
            $current['wo_num']  = 0;
            $current['wo_cost'] = 0;
        }

        //求查询时间段的出库量及成本
        $sql_current_do = "SELECT sum(`quantity`) as num,sum(cost) as cost FROM `deliveryorders` WHERE `sku`='".$big_sku."' AND  `delivery_date`>='".$date_from."' AND  delivery_date<='".$date_to." 23:59:59'";

        $current_do = DB::query(Database::SELECT,$sql_current_do)
            ->execute('newerp')
            ->as_array();

        if($current_do[0]['num']){
            $current['do_num'] = $current_do[0]['num'];
            //$supplier = new Service_Common();
            //$price = $supplier->get_sku_price($sku);
            $current['do_cost'] = round($current_do[0]['cost'],2);
        }else{
            $current['do_num']  = 0;
            $current['do_cost'] = 0;
        }

        $current['ending_num']  = $current['opening_num']+$current['wo_num']-$current['do_num'];
        $current['ending_cost'] = $current['opening_cost']+$current['wo_cost']-$current['do_cost'];

        return $current;
    }

    //通过查询开始时间和结束时间，查处这段时间的总库存成本数据
    public static function select_sum_num_cost($date_from,$date_to)
    {

        $day      = date_format(date_create($date_from),'d');
        $open_day = date_format(date_create($date_from),'Y-m-01');

        $current = array();

        //求每月的库存期初值
        $sql_openinventory = "SELECT SUM(quantity) AS num, SUM(cost) AS cost FROM inventories WHERE inventory_date ='".$open_day."'";
        $openinventory     = DB::query(Database::SELECT,$sql_openinventory)
            ->execute('newerp')
            ->as_array();
        if(isset($openinventory[0]['num'])){
            $current['opening_num']  = $openinventory[0]['num'];
            $current['opening_cost'] = round($openinventory[0]['cost'],2);
        }else{
            $current['opening_num']  = 0;
            $current['opening_cost'] = 0;
        }

        //加减查询的开始时间的出入库值，作为查询时间点的期初值
        if($day!=01){

            $sql_open_current_wo = "SELECT sum(`warehouse_quantity`)  as num,sum(cost) as cost FROM `warehouseorders` WHERE   `warehouse_date`>='".$open_day."' AND warehouse_date<'$date_from'";
            $open_current_wo     = DB::query(Database::SELECT,$sql_open_current_wo)
                ->execute('newerp')
                ->as_array();
            if(isset($open_current_wo[0]['num'])){
                $current['opening_num']  = $current['opening_num']+$open_current_wo[0]['num'];
                $current['opening_cost'] = round(($current['opening_cost']+$open_current_wo[0]['cost']),2);
            }

            $sql_open_current_do = "SELECT sum(quantity) as num,sum(cost) as cost FROM deliveryorders WHERE delivery_date>='".$open_day."' AND  delivery_date<'".$date_from."'";

            $open_current_do = DB::query(Database::SELECT,$sql_open_current_do)
                ->execute('newerp')
                ->as_array();

            if($open_current_do[0]['num']){
                $current['opening_num']  = $current['opening_num']-$open_current_do[0]['num'];
                $current['opening_cost'] = round(($current['opening_cost']-$open_current_do[0]['cost']),2);
            }
        }

        //求查询时间段的入库量及成本
        $sql_current_wo = "SELECT sum(`warehouse_quantity`)  as num,sum(cost) as cost FROM `warehouseorders` WHERE    warehouse_date>='".$date_from."' AND warehouse_date<='$date_to 23:59:59'";
        $current_wo     = DB::query(Database::SELECT,$sql_current_wo)
            ->execute('newerp')
            ->as_array();
        if(isset($current_wo[0]['num'])){
            $current['wo_num']  = $current_wo[0]['num'];
            $current['wo_cost'] = round($current_wo[0]['cost'],2);
        }else{
            $current['wo_num']  = 0;
            $current['wo_cost'] = 0;
        }

        //求查询时间段的出库量及成本
        $sql_current_do = "SELECT sum(`quantity`) as num,sum(cost) as cost FROM `deliveryorders` WHERE delivery_date>='".$date_from."' AND  delivery_date<='".$date_to." 23:59:59'";

        $current_do = DB::query(Database::SELECT,$sql_current_do)
            ->execute('newerp')
            ->as_array();

        if($current_do[0]['num']){
            $current['do_num']  = $current_do[0]['num'];
            $current['do_cost'] = round($current_do[0]['cost'],2);
        }else{
            $current['do_num']  = 0;
            $current['do_cost'] = 0;
        }

        $current['ending_num']  = $current['opening_num']+$current['wo_num']-$current['do_num'];
        $current['ending_cost'] = round(($current['opening_cost']+$current['wo_cost']-$current['do_cost']),2);

        return $current;
    }

    //查看不良品实际库存量
    public static function select_bad_product_stock($po_id,$sku)
    {
        $un_in  = 0;
        $un_out = 0;
        $un_sql = "SELECT sum(quantity) as num FROM `unqualified_deal_invoices` WHERE `sku`='".$sku."' AND `purchaseorder_id`='".$po_id."'";
        $un     = DB::query(Database::SELECT,$un_sql)
            ->execute('newerp');
        if(!empty($un)){

            $un_in = $un[0]['num'];
        }
        $unout_sql = "SELECT sum(`quantity`)  as num FROM `bad_product_delivery` WHERE `status`=20 AND `purchaseorder_id`='".$po_id."' AND `sku`='".$sku."'";
        $unout     = DB::query(Database::SELECT,$unout_sql)
            ->execute('newerp');
        if(!empty($unout)){
            $un_out = $unout[0]['num'];
        }
        return $un_in-$un_out;
    }
    /**
     * 查询单个sku库存
     *
     */
    public static function query_inventory($sku)
    {
        $result = DB::select('sku_qty')->from('stock_order')->where('sku','=',trim($sku))->execute('newerp')->get('sku_qty');
        return $result?$result:0;

    }

    /**
     * 根据供应商，仓库，主体查询单个sku库存
     *
     */
    public static function query_sku_inventory($sku,$supplier=null,$warehouse=null,$enter=null)
    {
        $date = date('Y-m-d');
        $where = " AND sku = '$sku'";
        if($supplier){
            $where .= " AND supplier_id = '$supplier'";
        }
        if($warehouse){
            $where .= " AND warehouseid = '$warehouse'";
        }
        if($enter){
            $where .= " AND enterprise_dominant = '$enter'";
        }
        $sql = "select a.sku,a.supplier_id,warehouseid,enterprise_dominant,sum(`quantity`) as qty
             from (
            SELECT `sku`, `stock` `quantity`,warehouseid,enterprise_dominant,supplier_id
                    FROM `sku_stocks`
                    WHERE  `date`='$date'
                    AND `stock` <>0
                    $where
                    UNION ALL
                    SELECT `sku`, `warehouse_quantity` `quantity`,warehouseid,enterprise_dominant,supplier_id
                    FROM `warehouseorders`
                    WHERE `warehouse_date`>='$date'
                   $where
                    UNION ALL
                    SELECT `sku`, -`quantity` `quantity`,warehouseid,enterprise_dominant,supplier_id
                    FROM `deliveryorders`
                    WHERE `delivery_date`>='$date'
                    $where
            ) a 
            LEFT JOIN vmi_supplier_skus b ON b.sku = a.sku AND b.supplier_id = a.supplier_id
            GROUP BY a.`sku`,a.warehouseid,a.enterprise_dominant,a.supplier_id ";
//        echo "<pre>";print_r($sql);echo "</pre>";die;
            $result = DB::query(Database::SELECT, $sql)->execute('newerp')->current();
//            echo '<pre>';print_r($sql);echo '</pre>';die;
            return $result?$result['qty']:0;
    }
    
    /**
     * 查询实时库存（区分仓库、主体、供应商），不含在途库存
     * @param int $warehouse_id 仓库ID
     * @param array $condition 查询条件["sku" => ["A301", "A302"], "supplier_id" => 1, "enterprise_dominant" => 1, "limit" => 30, "offset" => 0]
     */
    public static function get_real_stocks($warehouse_id = 0, $condition = array(), $count = 0) {
        $sql_0 = "SELECT max(date) AS date FROM sku_stocks LIMIT 1";
        $last_date = DB::query(Database::SELECT, $sql_0)->execute('newerp')->get('date');
        if(empty($last_date)) {
            $last_date = date('Y-m-d');
        }
        if($warehouse_id) {
            $where = ' AND warehouseid=' . intval($warehouse_id);
        }
        if(is_array($condition) && !empty($condition)) {
            if(isset($condition['supplier_id']) && !empty($condition['supplier_id'])) {
                $where .= " AND supplier_id=" . intval($condition['supplier_id']);
            }
            if(isset($condition['sku']) && !empty($condition['sku'])) {
                if(is_array($condition['sku']) && isset($condition['sku'][1])) {
                    $where .= " AND sku IN ('" . implode("','", array_map('addslashes', $condition['sku'])) . "')";
                } else {
                    $sku = is_array($condition['sku']) ? $condition['sku'][0] : $condition['sku'];
                    $where .= " AND sku='" . addslashes($sku) . "'";
                }
            }
            if(isset($condition['enterprise_dominant'])) {
                $where .= " AND enterprise_dominant=" . intval($condition['enterprise_dominant']);
            }
        }
        $limit = '';
        if(isset($condition['limit'])) {
            if(isset($condition['offset'])) {
                $limit = "LIMIT " . intval($condition['offset']) . ',' . intval($condition['limit']);
            } else {
                $limit = "LIMIT " . intval($condition['limit']);
            }
        }
        if($count) {
            $sql = "SELECT COUNT(1) AS total FROM (
                    SELECT 1 FROM (
                        SELECT sku,stock quantity,warehouseid,enterprise_dominant,supplier_id FROM sku_stocks WHERE date='{$last_date}' {$where}
                        UNION ALL
                        SELECT sku,warehouse_quantity quantity,warehouseid,enterprise_dominant,supplier_id FROM warehouseorders WHERE warehouse_date>='{$last_date}' {$where}
                        UNION ALL
                        SELECT sku, -quantity quantity,warehouseid,enterprise_dominant,supplier_id FROM deliveryorders WHERE delivery_date>='{$last_date}' {$where}
                    ) a 
                    GROUP BY a.`sku`,a.warehouseid,a.enterprise_dominant,a.supplier_id
                ) tmp";
            return DB::query(Database::SELECT, $sql)->execute('newerp')->get('total');
        } else {
            $sql = "SELECT a.sku,a.supplier_id,warehouseid,enterprise_dominant,SUM(`quantity`) as quantity
                 FROM (
                    SELECT sku,stock quantity,warehouseid,enterprise_dominant,supplier_id FROM sku_stocks WHERE date='{$last_date}' {$where}
                    UNION ALL
                    SELECT sku,warehouse_quantity quantity,warehouseid,enterprise_dominant,supplier_id FROM warehouseorders WHERE warehouse_date>='{$last_date}' {$where}
                    UNION ALL
                    SELECT sku, -quantity quantity,warehouseid,enterprise_dominant,supplier_id FROM deliveryorders WHERE delivery_date>='{$last_date}' {$where}
                ) a 
                GROUP BY a.`sku`,a.warehouseid,a.enterprise_dominant,a.supplier_id {$limit}";
            return DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
        }
    }
    
    /**
     * 查询动销数据（区分仓库、主体、供应商）
     * @param int $type 出库类型
     * @param array $condition 查询条件["sku" => ["A301", "A302"], "supplier_id" => 1, "enterprise_dominant" => 1, "limit" => 30, "offset" => 0]
     */
    public static function get_sales($type = 10, $condition = array()) {
        $where = array();
        if(is_array($condition) && !empty($condition)) {
            if(isset($condition['warehouseid']) && !empty($condition['warehouseid'])) {
                $where[] = "warehouseid=" . intval($condition['warehouseid']);
            }
            if(isset($condition['supplier_id']) && !empty($condition['supplier_id'])) {
                if(is_array($condition['supplier_id'])) {
                    $where[] = "supplier_id IN (" . implode(',', array_map('intval', $condition['supplier_id'])) . ")";
                } else {
                    $where[] = "supplier_id=" . intval($condition['supplier_id']);
                }
            }
            if(isset($condition['sku']) && !empty($condition['sku'])) {
                if(is_array($condition['sku']) && isset($condition['sku'][1])) {
                    $where[] = "sku IN ('" . implode("','", array_map('addslashes', $condition['sku'])) . "')";
                } else {
                    $sku = is_array($condition['sku']) ? $condition['sku'][0] : $condition['sku'];
                    $where[] = "sku='" . addslashes($sku) . "'";
                }
            }
            if(isset($condition['enterprise_dominant']) && !empty($condition['enterprise_dominant'])) {
                $where[] = "enterprise_dominant=" . intval($condition['enterprise_dominant']);
            }
            if(isset($condition['date'])) {
                $start_date = $end_date = '';
                if(isset($condition['date'][1])) {
                    $where[] = "delivery_date<'{$condition['date'][1]}' AND delivery_date>='{$condition['date'][0]}'";
                } else {
                    $start_date = is_array($condition['date']) ? $condition['date'][0] : $condition['date'];
                    $where[] = "delivery_date>='{$start_date}'";
                }
            }
        }
        $limit = '';
        if(isset($condition['limit'])) {
            if(isset($condition['offset'])) {
                $limit = "LIMIT " . intval($condition['offset']) . ',' . intval($condition['limit']);
            } else {
                $limit = "LIMIT " . intval($condition['limit']);
            }
        }
        $sql = "SELECT sku,SUM(quantity) AS quantity,warehouseid,enterprise_dominant,supplier_id FROM deliveryorders "
            . " WHERE type={$type} AND " . implode(' AND ', $where);
        $sql .= " GROUP BY sku,warehouseid,enterprise_dominant,supplier_id {$limit}";
        return DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
    }
    
    /**
     * 获取SKU库存，不区分仓库、主体、供应商，不含在途库存
     * @param array|string $skus
     * @param array $condition 查询条件
     */
    public static function get_sku_real_stocks($skus = array(), $condition = array()) {
        $stocks = array();
        if(!is_array($skus)) {
            $skus = explode(',', $skus);
        }
        if(!isset($skus[0])) {
            return $stocks;
        }
        $condition['sku'] = $skus;
        $results = self::get_real_stocks($condition['warehouse_id'], $condition);
        if(empty($results)) {
            return $stocks;
        }
        foreach($results as $row) {
            $stocks[$row['sku']] += $row['quantity'];
        }
        $diff = array_diff($skus, array_keys($stocks));
        if(isset($diff[0])) {
            foreach($diff as $sku) {
                $stocks[$sku] = 0;
            }
        }
        return $stocks;
    }
}
